package com.grouplay.dao;

import com.grouplay.entity.Group;
import com.grouplay.entity.GroupBudget;
import com.grouplay.entity.Invitation;
import com.grouplay.entity.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository("groupDao")
public interface IGroupDao {

    @Insert("insert into `group` (leaderId, groupName, description, totalBudget, personalBudget, beginTime, endTime, isEnd) values (#{leaderId}, #{groupName}, #{description}, #{totalBudget}, #{personalBudget}, #{beginTime}, #{endTime}, #{isEnd})")
    void insert(Group group);

    @Select("select \n" +
            "g.id id, \n" +
            "g.leaderId leaderId, \n" +
            "u.name leaderName, \n" +
            "g.groupName groupName,\n" +
            "g.description description,\n" +
            "g.totalBudget totalBudget,\n" +
            "g.personalBudget personalBudget,\n" +
            "g.beginTime beginTime, \n" +
            "g.isEnd isEnd \n" +
            "from \n" +
            "`group` g, \n" +
            "`user` u\n" +
            "where \n" +
            "#{id} = g.id and \n" +
            "g.leaderId = u.id")
    Group findGroupById(Integer id);

    /**
     * 按照创建者查找所有他创建的团队
     * @param leaderId
     * @return
     */
    @Select("select * from `group` where leaderId = #{leaderId} order by isEnd")
    List<Group> findAllGroupsByLeaderId(Integer leaderId);

    /**
     * 按照创建者查找所有他创建的未结束团队
     * @param leaderId
     * @return
     */
    @Select("select * from `group` where leaderId = #{leaderId} and isEnd = 0")
    List<Group> findNotEndGroupsByLeaderId(Integer leaderId);

    /**
     * 按照创建者查找所有他创建的已结束团队
     * @param leaderId
     * @return
     */
    @Select("select * from `group` where leaderId = #{leaderId} and isEnd = 1")
    List<Group> findIsEndGroupsByLeaderId(Integer leaderId);

    /**
     * 按照团队名查找未结束的团队
     * @param groupName
     * @return
     */
    @Select("select * from `group` where groupName = #{groupName} and isEnd = 0")
    Group findGroupsByGroupName(String groupName);

    /**
     * 查找一个团队除了团长之外的其他用户
     * @param group
     * @return
     */
    @Select("select u.id id, u.name name from `user` u, `user_group` ug, `group` g where g.id = #{id} and g.leaderId = #{leaderId} and g.isEnd = 0 and ug.gid = #{id} and u.id = ug.uid")
    List<User> findAllUserNameOfTheGroupByGroupWhileIsNotEnd(Group group);

    @Insert("insert into `invitation` (groupId, targetUid, date, hasBeenLooked) values (#{groupId}, #{targetUid}, #{date}, #{hasBeenLooked})")
    void sendOneInvitation(Invitation invitation);

    @Update("update `group` set totalBudget = #{totalBudget}, personalBudget = #{personalBudget} where id = #{id} and leaderId = #{leaderId}")
    void updateGroupBudget(Group group);

    @Select("select \n" +
            "g.id id, \n" +
            "g.leaderId leaderId,\n" +
            "u.name leaderName, \n" +
            "g.groupName groupName, \n" +
            "g.description description,\n" +
            "g.totalBudget totalBudget,\n" +
            "g.personalBudget personalBudget,\n" +
            "g.beginTime beginTime,\n" +
            "g.endTime endTime,\n" +
            "g.isEnd isEnd\n" +
            "from \n" +
            "`group` g, \n" +
            "`user_group` ug, \n" +
            "`user` u \n" +
            "where \n" +
            "ug.uid = #{uid} and \n" +
            "ug.gid = g.id and \n" +
            "g.leaderId = u.id \n" +
            "order by g.isEnd")
    List<Group> findMyJoinGroupsByUid(Integer uid);

    @Update("update `group` set endTime = #{endTime}, isEnd = 1 where id = #{id} and leaderId = #{leaderId} and isEnd = 0")
    void endGroup(Group group);

    @Select("select \n" +
            "g.id id, \n" +
            "g.leaderId leaderId,\n" +
            "u.name leaderName, \n" +
            "g.groupName groupName, \n" +
            "g.description description,\n" +
            "g.totalBudget totalBudget,\n" +
            "g.personalBudget personalBudget,\n" +
            "g.beginTime beginTime,\n" +
            "g.endTime endTime,\n" +
            "g.isEnd isEnd\n" +
            "from \n" +
            "`group` g, \n" +
            "`user_group` ug, \n" +
            "`user` u \n" +
            "where \n" +
            "ug.uid = #{uid} and \n" +
            "ug.gid = g.id and \n" +
            "g.leaderId = u.id and \n" +
            "g.isEnd = 1 and \n" +
            "ug.isPay is null\n")
    List<Group> findMyJoinGroupsIsEndAndIsNotPay(Integer uid);

    @Select("select \n" +
            "g.groupName groupName,\n" +
            "u.name payer,\n" +
            "money payMoney,\n" +
            "g.totalBudget totalBudget,\n" +
            "0 isLeader \n" +
            "from \n" +
            "`group` g,\n" +
            "user_group ug  left outer join transferrecord on (transferrecord.sourceId = ug.uid and transferrecord.groupId = ug.gid) ,\n" +
            "user u \n" +
            "where \n" +
            "g.id = #{groupId} and \n" +
            "g.id = ug.gid and \n" +
            "u.id = ug.uid ")
    List<GroupBudget> findGroupBudgetByGroupId(Integer groupId);
}
